IF OBJECT_ID('dbo.RptCSCRPendingClinicalTrial') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRPendingClinicalTrial
    IF OBJECT_ID('dbo.RptCSCRPendingClinicalTrial') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRPendingClinicalTrial >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRPendingClinicalTrial >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptCSCRPendingClinicalTrial
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 6/19/2007
--
-- Description: Source of Report, "CSCRPendingClinicalTrial"
--                  In Insight DB Reporting Services
--
-- Used by CSCR management, agreement associates and budget analysts 
-- to track and monitor clinical trial agreements currently under development. 
-- The CSCR associate director runs this report for biweekly meetings to discuss 
-- the status of work in process.
--
-- Parameters:  
--             @StartDate          CreatedDate
--             @EndDate            CreatedDate
--             @Sponsor         
--             @Institution     
--             @CSCRCaseManager 
--             @SponsorType     
--             @PreAwardManager 
--             @AgreementType   
--             @ActivityType    
--             @domainUserId    
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptCSCRPendingClinicalTrial()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 6/19/07     KC        Created
-- 8/8/2007    KC        Fixed Naming Conventions, DomainUserId
-- 9/11/07     KC        Changed to new source tables
-- 9/13/07     KC        Using new CSCR Deliverables View
-- 9/14/07     KC        Added Status Flattened View for group header area.
--                       fixed issue with ('ALL','','0')
-- 9/17/07     KC        Security was using old function. Fixed.
-- 9/20/07     KC        Removed Start and End Dates, and @ActivityType
-- 9/21/07     KC        Changed Params to INTs (Inst,Dept,Unit)
-- 9/24/07     KC        Added ManagerId's to end of result set
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/3/07     KC        Added AI.ActivityDescription to resultset,
--                       and 'OTHER' to where clause
-- 11/28/07    KC        Added 'CSCR - On Hold' as a PendingStatus as well.
-- 11/29/07    KC        Added DaysDiff to AmendmentStatus
-- 02/27/08    KC        Removed Security per Partners Request
-- 03/26/08    KC        Added SubContractStatus and SubContractStatusDate
-- 04/09/08    KC        Added @IncludeDanaFarber referring to Institution
-- 05/13/08    KC        Added Deliverable, 'Working Budget Amendments'
-- 06/05/08    KC        Added filter, DTS.CSCRWorkingBudgetAmendmentStatus 
--                            IN ('Pending','CSCR - On Hold')
--
-- =======================================================================
CREATE PROCEDURE dbo.RptCSCRPendingClinicalTrial
     (
     @Institution                       VARCHAR(100) = NULL,
     @CSCRCaseManager                   VARCHAR(100) = NULL,             
     @PreAwardManager                   VARCHAR(100) = NULL,
     @AgreementType                     VARCHAR(100) = NULL,
     @IncludeDanaFarber                 VARCHAR(3) = NULL,   
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT

     -- ALL checking
     IF @Institution IN ('ALL','','0',NULL) SET @Institution = NULL
     IF @CSCRCaseManager IN ('ALL','','0',NULL) SET @CSCRCaseManager = NULL
     IF @PreAwardManager IN ('ALL','','0',NULL) SET @PreAwardManager = NULL
     IF @AgreementType IN ('ALL','','0',NULL) SET @AgreementType = NULL
     IF @IncludeDanaFarber IN ('Yes','1','Y') SET @IncludeDanaFarber = NULL ELSE SET @IncludeDanaFarber = '2'
     
     -- Result Set
     SELECT    AI.FolderNumber AS ProposalNumber,        
               AI.ProposalTitle AS ProposalTitle, 
               AI.ReceivedDate AS CreatedDate,
               AI.Institution AS Institution,
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               AI.SponsorName AS PrimarySponsorName,        
               AI.SecondarySponsor AS SecondarySponsorName,
               AI.CSCRCaseManager AS CSRCaseManager,
               AI.CSCRCaseManagerName AS CSRCaseManagerName,
               AI.PreAwardManager AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,'Pre Award Manager Not Assigned') AS PreAwardManagerName,
               
               DTS.CSCRPIQStatus AS PIQStatus,
               DTS.CSCRPIQStatusDate AS PIQStatusDate,
               DTS.CSCRContractStatus AS ContractStatus,
               DTS.CSCRContractStatusDate AS ContractStatusDate,
               DTS.CSCRWorkingBudgetStatus AS WorkingBudgetStatus,
               DTS.CSCRWorkingBudgetStatusDate AS WorkingBudgetStatusDate,
               --DTS.CSCRAmendmentStatus AS AmendmentStatus,
               CASE WHEN DTS.CSCRAmendmentStatus = 'Pending'
                    THEN DTS.CSCRAmendmentStatus + ' (' + CONVERT(VARCHAR(10),DATEDIFF(DD,DTS.CSCRAmendmentStatusDate,GETDATE())) + ' days)'
                    ELSE DTS.CSCRAmendmentStatus
               END AS AmendmentStatus,
               DTS.CSCRAmendmentStatusDate AS AmendmentStatusDate,
               DTS.CSCRSubContractStatus AS SubContractStatus,
               DTS.CSCRSubContractStatusDate AS SubContractStatusDate,
               DTS.CSCRWorkingBudgetAmendmentStatus AS WorkingBudgetAmendmentStatus,
               DTS.CSCRWorkingBudgetAmendmentStatusDate AS WorkingBudgetAmendmentStatusDate,
               DATEDIFF(dd,AI.ReceivedDate,GETDATE()) AS ElapsedTime,
               COM.CommentSort AS CommentSort,
               COM.CommentType AS CommentType,
               COM.CommentDate AS CommentDate,
               COM.Comments AS Comments,
               
               AI.AgreementType AS AgreementType,
               AI.InstrumentType AS ProposalType,
               AI.ProposalStatus AS ProposalStatus,
               AI.ActivityDescription AS ActivityDescription,
               UDF.ActivityType AS UDFActivityType,
               UDF.SubActivityType AS SubActivityType,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.SponsorId AS SponsorId,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreementId,
               AI.FolderNumber AS FolderNumber,
               AI.CSCRManagerId AS CSCRManagerId,
               AI.PreAwardManagerId AS PreAwardManagerId
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN

               -- Get the Latest or Current status and date for each Deliverable Type
               dbo.vwInfoEdCSCRDeliverableTypeStatus DTS ON AI.InfoEdPropNumber = DTS.prop_no LEFT OUTER JOIN
             
               -- These get all the Comments by Type..               
               (SELECT   prop_no,
                         CASE REPORTCAT_CODE
                              WHEN 'REPORTCAT_18' THEN 1
                              WHEN 'REPORTCAT_19' THEN 2
                              WHEN 'REPORTCAT_30' THEN 3
                              WHEN 'REPORTCAT_33' THEN 4
                              WHEN 'REPORTCAT_34' THEN 5
                              ELSE 6    -- CSCR - Working Budget Amendment
                         END AS CommentSort,
                         CASE REPORTCAT_CODE
                              WHEN 'REPORTCAT_18' THEN 'PIQ Comments'
                              WHEN 'REPORTCAT_19' THEN 'Contract Comments'
                              WHEN 'REPORTCAT_30' THEN 'Working Budget Comments'
                              WHEN 'REPORTCAT_33' THEN 'Amendment Comments'
                              WHEN 'REPORTCAT_34' THEN 'SubContract Comments'
                              ELSE 'Working Budget Amendments Comments'
                         END AS CommentType,
                         StatusDate as CommentDate,
                         Comments
               FROM      dbo.vwInfoEdCSCRDeliverables
               ) COM ON AI.InfoEdPropNumber = COM.prop_no LEFT OUTER JOIN
               
               dbo.InfoEdUDFProposal UDF ON AI.InfoEdPropNumber = UDF.PropNumber
     WHERE     (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@CSCRCaseManager IS NULL OR AI.CSCRManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @CSCRCaseManager ))) AND
               (@PreAwardManager IS NULL OR AI.PreAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PreAwardManager ))) AND
               (@AgreementType IS NULL OR AI.AgreementType IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @AgreementType ))) AND
               (RTRIM(AI.ActivityDescription) = 'Clinical Research' 
               OR (UDF.ActivityType LIKE 'Other%' AND UDF.SubActivityType LIKE 'Clinical Services%')) AND
               
               -- Same for all Agreements: CaseManager cannot be null and Status is Pending
               AI.CSCRCaseManagerName IS NOT NULL AND RTRIM(AI.CSCRCaseManagerName) != '' AND
               
               -- If MasterStatus is Pending, return it even if all Deliverables are Closed
               (AI.MasterProposalStatus = 'Pending' 
               
               -- If any of the Deliverables are Pending or CSCR - On Hold, return it.
               OR DTS.CSCRPIQStatus IN ('Pending','CSCR - On Hold') 
               OR DTS.CSCRContractStatus IN ('Pending','CSCR - On Hold') 
               OR DTS.CSCRWorkingBudgetStatus IN ('Pending','CSCR - On Hold') 
               OR DTS.CSCRAmendmentStatus IN ('Pending','CSCR - On Hold')
               OR DTS.CSCRSubContractStatus IN ('Pending','CSCR - On Hold')
               OR DTS.CSCRWorkingBudgetAmendmentStatus IN ('Pending','CSCR - On Hold')) AND
               -- debugging
               --AND (AI.FolderNumber = @FolderNumber OR @FolderNumber IS NULL)
               
               -- Add like ActivityReport and Senior Leadership Report
               (@IncludeDanaFarber IS NULL OR AI.InstitutionId NOT IN (350,351))                
               
               
     ORDER BY AI.CSCRCaseManagerName, AI.PreAwardManagerName, AI.FolderNumber, COM.CommentSort, COM.CommentDate
               


     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptCSCRPendingClinicalTrial') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRPendingClinicalTrial >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRPendingClinicalTrial >>>'
go

GRANT EXECUTE ON dbo.RptCSCRPendingClinicalTrial TO InsightReport,Insight
go
